A New Hybrid Predicate-Assertion-Index-Locking (PAX) Algorithm to Prevent Transactional Phantoms

We discuss three forms of phantom prevention locking that have been proposed in the literature: (1) predicate locking [EGLT76], (2) precision locking [JBB81], and (3) index locking (specifically, KVL/IM locking [MOHAN90, MOHAN92, MOHAN96]). The predicate locking method was dropped from consideration in System R because of perceived problems [CHETAL81]. The precision locking technique was proposed as an alternative, but was thought to require excessive resource use in some application regimes. Only index locking methods are now being used by major commercial database systems. The current paper introduces a novel and workable extension of KVL/IM locking, to provide increased concurrency in certain application regimes by an alternative phantom prevention method that "asserts" read predicates and updates in memory, a technique based on precision locking which we call predicate assertion, or "PA". PA is more precise than index locking in many cases, reducing the number of false conflicts between transactions, and improves on index locking CPU overhead in certain application regimes where complex queries outnumber update statements. Using PA as a runtime alternative to index locking gives us a hybrid Predicate-Assertion-Index-locking scheme, which we abbreviate "PAX". A graceful method of quickly converting from KVL locking to predicate assertion and back during runtime is explained, along with a low-cost statistical method by which a runtime system can recognize regimes where conversion is appropriate.